package com.mini.mysql.repository;

import com.mini.mysql.model.BusinessBaseSubInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 项目名称：mini-data-es
 * 类 名 称：BusinessLessonInfoRepository
 * 类 描 述：TODO
 * 创建时间：2021/4/1 下午6:40
 * 创 建 人：zhanghuiying
 */
@Repository
public interface BusinessBaseSubInfoRepository extends JpaRepository<BusinessBaseSubInfo, Long>, JpaSpecificationExecutor<BusinessBaseSubInfo> {

    @Query(value = "select b.mini_lesson_id," +
            "b.mini_lesson_name, " +
            "b.lesson_name," +
            "b.lesson_id," +
            "b.site_item,"+
            "b.xingtu_site," +
            "b.un_pay_site_id," +
            "b.pay_site_id," +
            "b.business_type," +
            "c.item_name," +
            "l.sku_name," +
            "b.group_names \n " +
            "from business_base_info b " +
            "left join business_lesson_info l on b.lesson_id=l.lesson_id " +
            "left join business_item_info c on b.site_item=c.id \n " +
            "where b.mini_lesson_id in (?1)",nativeQuery = true)
    List<BusinessBaseSubInfo> findBaseSub(List<Long> ids);

    @Query(value = "select b.mini_lesson_id," +
            "b.mini_lesson_name, " +
            "b.lesson_name," +
            "b.lesson_id," +
            "b.site_item,"+
            "b.xingtu_site," +
            "b.un_pay_site_id," +
            "b.pay_site_id," +
            "b.business_type," +
            "c.item_name," +
            "l.sku_name," +
            "b.group_names \n " +
            "from business_base_info b " +
            "left join business_lesson_info l on b.lesson_id=l.lesson_id " +
            "left join business_item_info c on b.site_item=c.id \n " +
            "where b.mini_lesson_id = ?2 or b.mini_lesson_name = ?1 " +
            "or b.pay_site_id = ?1 or b.un_pay_site_id = ?1 ",nativeQuery = true)
    List<BusinessBaseSubInfo> findAllByParamAndMiniLessonId(String key, Long mini_lesson_id);


    @Query(value = "select b.mini_lesson_id," +
            "b.mini_lesson_name, " +
            "b.lesson_name," +
            "b.lesson_id," +
            "b.site_item,"+
            "b.xingtu_site," +
            "b.un_pay_site_id," +
            "b.pay_site_id," +
            "b.business_type," +
            "c.item_name," +
            "l.sku_name," +
            "b.group_names \n " +
            "from business_base_info b " +
            "left join business_lesson_info l on b.lesson_id=l.lesson_id " +
            "left join business_item_info c on b.site_item=c.id \n " +
            "where b.mini_lesson_name = ?1 " +
            "or b.pay_site_id = ?1 or b.un_pay_site_id = ?1 ",nativeQuery = true)
    List<BusinessBaseSubInfo> findAllByParam(String key);
}
